----------------------------------------------
-- XQUERY
----------------------------------------------

DECLARE @xmldoc xml
SET @xmldoc = '<people>
 <person>
  <name>
   <givenName>Martin</givenName>
   <familyName>Gudgin</familyName>
  </name>
  <age>33</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Simon</givenName>
   <familyName>Horrell</familyName>
  </name>
  <age>40</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Mark</givenName>
   <familyName>Szolkowski</familyName>
  </name>
  <age>30</age>
  <height>medium</height>
 </person>
</people>'

PRINT 'Raw Document'
SELECT @xmldoc

-- Return names

PRINT 'Get each person''s name'

SELECT @xmldoc.query(' 
(: SQL Server 2005 :)
(: doc function not used :)
for $p in /people/person
return $p/name') 

-- 

PRINT 'Return people older than 30 (XPATH way)'

SELECT @xmldoc.query(' 
(: this uses an XPath predicate :)
/people/person[age > 30]')

--

PRINT 'Return people older than 30 (FLWOR way)'

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return $p/name')

-- Getting Creative

PRINT 'Return people older than 30, order by Age, show the name in HTML (FLWOR way)'

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return <foo><bar>{$p/name}</bar></foo>')
